
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
-- Stored Procedure

CREATE Procedure  [dbo].[SP_TCMOVI_ENRE_Q01]              
/*-------------------------------------------------------*/              
/*---Empresa              : OFISIS S.A.                --*/              
/*---Cliente              : OFISIS S.A.         --*/              
/*---Sistema              : Tesoreria                  --*/              
/*---M«dulo               : Empresa                    --*/              
/*---Programa             : Cuentas Corrientes        --*/              
/*---Script               : tmdocu_p.sql               --*/              
/*---Nombre SP            : SP_TCMOVI_ENRE_Q01        --*/              
/*---Desarrollado por     : Eduardo Vivar A.           --*/              
/*---Fecha Creaci«n       : 15/06/2000                 --*/              
/*---Base Datos           : Microsoft Sql Server       --*/              
/*---Versi«n              : 7.0                        --*/              
/*---Invoca a SP          :                            --*/              
/*-------------------------------------------------------*/              
/*-------------------------------------------------------*/              
/*---Modificado 1 por     :          --*/              
/*---Fecha Modificaci«n   :          --*/               
/*---Detalle Modificaci«n :          --*/               
/*-------------------------------------------------------*/              
/*-------------------------------------------------------*/              
/*--- Drop Proc SP_TCMOVI_ENRE_Q01         --*/              
/*---Grant all on SP_TCMOVI_ENRE_Q01 to public         --*/               
/*--- SP_TCMOVI_ENRE_Q01 '01', 'E', '00000003', 'SOL', '01/01/2000','12/12/2000','T'   --*/              
/*--- SP_TCMOVI_ENRE_Q01 'SOL', '01/01/2010','12/12/2011','T', NULL, NULL, NULL,''   --*/              
/*-------------------------------------------------------*/              

 @ISCO_MONE      TD_VC_003,               
 @IDFE_INIC  TD_DT_001,              
 @IDFE_FINA  TD_DT_001,              
 @ISTI_REPO  TD_VC_001,              
 @ISCO_BANC      TD_VC_003,              
 @ISNU_CNTA_BANC TD_VC_020,              
 @ISCO_CAJA      TD_VC_008,              
 @ISCA_WHER_LOCA VARCHAR(500)               
 as              

CREATE TABLE #TWCNTA_CRTE               
  (CO_EMPR      VARCHAR (02) NULL,              
   TI_ENTI VARCHAR(1) NULL,                
   DE_TIPO_ENTI VARCHAR(50) NULL,              
   CO_ENTI VARCHAR(20) NULL,               
   DE_ENTI  VARCHAR(80) NULL,              
   CO_CABA_0001 VARCHAR(08) NULL,              
   NU_CNTA_0001 VARCHAR(20) NULL,              
   AA_0001 VARCHAR(04) NULL,              
   MM_0001 VARCHAR(02) NULL,              
   NU_COMP_0001 VARCHAR(08) NULL,              
   TI_MOVI_0001 VARCHAR(1) NULL,              
   FE_OPER_0001 DATETIME NULL,              
   TI_OPER_0001 VARCHAR(03) NULL,               
   CO_MONE_0001 VARCHAR (03) NULL,              
   IM_MOVI_0001 Numeric(16,4) NULL,              

   TI_DOCU_0001 VARCHAR(03) NULL,               
   NU_DOCU_0001 VARCHAR(15) NULL,               
   CO_BCHE_0001 VARCHAR(03) NULL,               
   NU_CHEQ_0001 VARCHAR(15) NULL,              

   NU_REND_GAST VARCHAR(10) NULL,               
   CO_CABA_0002 VARCHAR(08) NULL,              
   NU_CNTA_0002 CHAR(20) NULL,              
   AA_0002 VARCHAR(04) NULL,              
   MM_0002 VARCHAR(02) NULL,              
   NU_COMP_0002 VARCHAR(08) NULL,              
   TI_MOVI_0002 VARCHAR(1) NULL,              
   FE_OPER_0002 DATETIME NULL,               
   TI_OPER_0002 VARCHAR(03) NULL,               
   CO_MONE_0002 VARCHAR (03) NULL,              
   IM_MOVI_0002 Numeric(16,4) NULL,              

   TI_DOCU_0002 VARCHAR(03) NULL,               
   NU_DOCU_0002 VARCHAR(15) NULL,               
   CO_BCHE_0002 VARCHAR(03) NULL,               
   NU_CHEQ_0002 VARCHAR(15) NULL,               

   NU_AUXI Numeric(16,4) NULL,              
   TI_ORIG VARCHAR(01) NULL,              
   TI_DATO VARCHAR(01) NULL,              
   DE_NOMB_BANC VARCHAR(100) NULL)             

DECLARE  @VSCA_SELE       TD_VC_100,                
         @VSFE_INIC       TD_VC_010,               
 @VSFE_FINA       TD_VC_010,              
  @VSCA_BANC_0001  TD_VC_100,              
  @VSCA_CAJA_0001  TD_VC_100,              
  @VSCA_BANC_0002  TD_VC_100,            
  @VSCA_CAJA_0002  TD_VC_100                  

-- select  @ISCA_WHER_LOCA = REPLACE(@ISCA_WHER_LOCA ,'TMEMPR','T1')              
-- select  @ISCA_WHER_LOCA = REPLACE(@ISCA_WHER_LOCA ,'TMAUXI_EMPR.TI_AUXI_EMPR','T1.TI_ENTI')               
-- select  @ISCA_WHER_LOCA = REPLACE(@ISCA_WHER_LOCA ,'TMAUXI_EMPR.CO_AUXI_EMPR','T1.CO_ENTI')            

 select  @ISCA_WHER_LOCA = REPLACE(@ISCA_WHER_LOCA ,'TCMOVI_BANC', 'T1')              
 select  @ISCA_WHER_LOCA = REPLACE(@ISCA_WHER_LOCA ,'TCMOVI_CAJA', 'T1')               
 select  @ISCA_WHER_LOCA = REPLACE(@ISCA_WHER_LOCA ,'TCMOVI_BACA', 'T1')            

    Select @VSCA_SELE = ''              
    Select @VSCA_BANC_0001 = ''              
    Select @VSCA_CAJA_0001 = ''              
    Select @VSCA_BANC_0002 = ''              
    Select @VSCA_CAJA_0002 = ''              



  /*  If @ISCO_EMPR IS NOT NULL              
       Select @VSCA_SELE = @VSCA_SELE + ' AND T1.CO_EMPR =  "'+@ISCO_EMPR+'"  '              
    If @ISTI_AUXI_EMPR IS NOT NULL              
       Select @VSCA_SELE = @VSCA_SELE + ' AND T1.TI_ENTI =  "' + @ISTI_AUXI_EMPR + '" '               
If @ISCO_AUXI_EMPR IS NOT NULL              
       Select @VSCA_SELE = @VSCA_SELE + ' AND T1.CO_ENTI =  RTRIM("' + @ISCO_AUXI_EMPR + '")  '              
*/              
    If @ISCO_BANC IS NOT NULL               
    BEGIN              
       Select @VSCA_BANC_0001 = @VSCA_BANC_0001 + ' AND T1.CO_BANC =  RTRIM("' + @ISCO_BANC + '")  '                  
       Select @VSCA_BANC_0002 = @VSCA_BANC_0002 + ' AND T5.CO_BANC =  RTRIM("' + @ISCO_BANC + '")  '                  
     END              
    If @ISNU_CNTA_BANC IS NOT NULL               
     BEGIN              
       Select @VSCA_BANC_0001 = @VSCA_BANC_0001 + ' AND T1.NU_CNTA_BANC =  RTRIM("' + @ISNU_CNTA_BANC + '")  '                  
       Select @VSCA_BANC_0002 = @VSCA_BANC_0002 + ' AND T5.NU_CNTA_BANC =  RTRIM("' + @ISNU_CNTA_BANC + '")  '                  
     END              
    If @ISCO_CAJA IS NOT NULL               
     BEGIN              
       Select @VSCA_CAJA_0001 = @VSCA_CAJA_0001 + ' AND T1.CO_CAJA =  RTRIM("' + @ISCO_CAJA + '")  '                  
       Select @VSCA_CAJA_0002 = @VSCA_CAJA_0002 + ' AND T3.CO_CAJA =  RTRIM("' + @ISCO_CAJA + '")  '                  
     END              

    Select @VSFE_INIC = Convert (varchar(10), @IDFE_INIC, 103)              
    Select @VSFE_FINA = Convert (varchar(10), @IDFE_FINA, 103)              

/*-----------------------   DATOS ---------------------------------*/                    




IF @ISTI_REPO = 'T' OR @ISTI_REPO = 'B'              
BEGIN              
-- ENTREGAS A RENDIR (HACER JOIN CON TIPO DE OPERACION)              
    EXECUTE (              
 'INSERT #TWCNTA_CRTE(CO_EMPR, TI_ENTI, CO_ENTI, DE_ENTI,               
 CO_CABA_0001, NU_CNTA_0001, AA_0001, MM_0001, NU_COMP_0001,               
 TI_MOVI_0001, FE_OPER_0001, TI_OPER_0001, CO_MONE_0001, IM_MOVI_0001,               
 TI_DOCU_0001, NU_DOCU_0001, CO_BCHE_0001, NU_CHEQ_0001,              
 NU_AUXI, TI_ORIG, TI_DATO, DE_NOMB_BANC)               
 SELECT T1.CO_EMPR, T1.TI_ENTI, T1.CO_ENTI, T1.DE_NOMB_ORDE,               
 T1.CO_BANC, T1.NU_CNTA_BANC, T1.AA_BNCO, T1.MM_BNCO, T1.NU_COMP_BANC,               
 T1.TI_MOVI_BANC, T1.FE_OPER, T1.CO_TIPO_OPER, T3.CO_MONE,               
 ROUND(              
 ( PATINDEX(T3.CO_MONE, "'+@ISCO_MONE+'") +              
 PATINDEX("0", CONVERT( CHAR(1), PATINDEX(T3.CO_MONE, "'+@ISCO_MONE+'")))*              
 ( PATINDEX(T3.CO_MONE, T4.CO_MONE_DEFA)/T1.FA_TIPO_CAMB  +              
   PATINDEX(T3.CO_MONE, T4.CO_MONE_EXTR)*T1.FA_TIPO_CAMB ))*ISNULL(T1.IM_MOVI,0) , 2),               
 T1.CO_TIPO_DOCU, T1.NU_DOCU_BANC, T1.CO_BANC_CHEQ, T1.NU_CHEQ,              
 1, "B", "1", T5.DE_NOMB_BANC              
 FROM TCMOVI_BANC T1, TTTIPO_OPER T2, TMCNTA_BANC T3, TMPARA_TESO T4, TTBANC_FINA T5             WHERE T1.TI_MOVI_BANC = "E"              
 '+ @ISCA_WHER_LOCA + @VSCA_SELE + @VSCA_BANC_0001 + '              
 AND T1.FE_OPER <= CONVERT(DATETIME, "'+ @VSFE_FINA +'", 103)              
 AND T1.TI_SITU != "ANU"              
 AND T1.CO_EMPR = T2.CO_EMPR              
 AND T1.CO_TIPO_OPER = T2.CO_TIPO_OPER           
 AND T2.ST_ENRE_OTRO = "S"              
 AND T1.CO_EMPR = T3.CO_EMPR              
 AND T1.CO_BANC = T3.CO_BANC              
 AND T1.NU_CNTA_BANC = T3.NU_CNTA_BANC              
 AND T1.CO_EMPR = T4.CO_EMPR              

 AND T1.CO_BANC = T5.CO_BANC              

 AND T1.CO_BANC_ENRE IS NULL              
 AND T1.NU_CNTA_ENRE IS NULL              
 AND T1.AA_ENRE IS NULL                      
 AND T1.MM_ENRE IS NULL               
 AND T1.NU_COMP_ENRE IS NULL              
 AND T1.NU_REND_GAST IS NULL')               

-- EGRESOS/INGRESOS -> REEMBOLSOS/DEVOLUCIONES (HACER JOIN CON TIPO DE OPERACION)              

 -- POR BANCOS               
 EXECUTE (              
 'INSERT #TWCNTA_CRTE(CO_EMPR, TI_ENTI, CO_ENTI, DE_ENTI,               
 CO_CABA_0001, NU_CNTA_0001, AA_0001, MM_0001, NU_COMP_0001,               
 TI_MOVI_0001, FE_OPER_0001,               
 NU_REND_GAST,               
 CO_CABA_0002, NU_CNTA_0002, AA_0002, MM_0002, NU_COMP_0002,               
 TI_MOVI_0002, FE_OPER_0002, TI_OPER_0002, CO_MONE_0002,              
 IM_MOVI_0002,               
 TI_DOCU_0002, NU_DOCU_0002, CO_BCHE_0002, NU_CHEQ_0002,              
 NU_AUXI, TI_ORIG, TI_DATO )               
 SELECT T1.CO_EMPR, T1.TI_ENTI, T1.CO_ENTI, T1.DE_NOMB_ORDE,               
 T1.CO_BANC_ENRE, T1.NU_CNTA_ENRE, T1.AA_ENRE, T1.MM_ENRE, T1.NU_COMP_ENRE,               
 "E", T5.FE_OPER,               
 T1.NU_REND_GAST,                
 T1.CO_BANC, T1.NU_CNTA_BANC, T1.AA_BNCO, T1.MM_BNCO, T1.NU_COMP_BANC,              
 T1.TI_MOVI_BANC, T1.FE_OPER, T1.CO_TIPO_OPER, T3.CO_MONE,               
 ROUND(              
 ( PATINDEX(T3.CO_MONE, "'+@ISCO_MONE+'") +              
 PATINDEX("0", CONVERT( CHAR(1), PATINDEX(T3.CO_MONE, "'+@ISCO_MONE+'")))*              
 ( PATINDEX(T3.CO_MONE, T4.CO_MONE_DEFA)/T1.FA_TIPO_CAMB  +              
   PATINDEX(T3.CO_MONE, T4.CO_MONE_EXTR)*T1.FA_TIPO_CAMB ))*ISNULL(T1.IM_MOVI,0), 2),               
 T5.CO_TIPO_DOCU, T5.NU_DOCU_BANC, T5.CO_BANC_CHEQ, T5.NU_CHEQ,              
 ( PATINDEX(T1.TI_MOVI_BANC,"E")- PATINDEX(T1.TI_MOVI_BANC,"I")) , "B", "2"              
 FROM TCMOVI_BANC T1, TTTIPO_OPER T2, TMCNTA_BANC T3, TMPARA_TESO T4, TCMOVI_BANC T5              
 WHERE T1.CO_EMPR = T2.CO_EMPR              
 '+@ISCA_WHER_LOCA+ @VSCA_SELE + @VSCA_BANC_0002 + '              
 AND T5.FE_OPER <= CONVERT(DATETIME, "'+ @VSFE_FINA +'", 103)              
 AND T1.TI_SITU != "ANU"              
 AND T5.TI_SITU != "ANU"              
 AND T1.CO_TIPO_OPER = T2.CO_TIPO_OPER              
 AND T2.ST_ENRE_OTRO = "S"              
 AND T1.CO_EMPR = T3.CO_EMPR              
 AND T1.CO_BANC = T3.CO_BANC              
 AND T1.NU_CNTA_BANC = T3.NU_CNTA_BANC              
 AND T1.CO_EMPR = T4.CO_EMPR              
 AND T1.CO_BANC_ENRE IS NOT NULL              
 AND T1.NU_CNTA_ENRE IS NOT NULL              
 AND T1.AA_ENRE IS NOT NULL                      
 AND T1.MM_ENRE IS NOT NULL               
 AND T1.NU_COMP_ENRE IS NOT NULL              


 AND T1.CO_EMPR = T5.CO_EMPR              
 AND T1.CO_BANC_ENRE = T5.CO_BANC              
 AND T1.NU_CNTA_ENRE = T5.NU_CNTA_BANC              
 AND T1.AA_ENRE = T5.AA_BNCO              
 AND T1.MM_ENRE = T5.MM_BNCO              
 AND T1.NU_COMP_ENRE = T5.NU_COMP_BANC              
 AND T5.TI_MOVI_BANC = "E"  ')              

 -- POR CAJA              
    EXECUTE (              
 'INSERT #TWCNTA_CRTE(CO_EMPR, TI_ENTI, CO_ENTI, DE_ENTI,               
 CO_CABA_0001, NU_CNTA_0001, AA_0001, MM_0001, NU_COMP_0001,               
 TI_MOVI_0001, FE_OPER_0001, NU_REND_GAST,               
 CO_CABA_0002, AA_0002, MM_0002, NU_COMP_0002,               
 TI_MOVI_0002, FE_OPER_0002, TI_OPER_0002, CO_MONE_0002,              
 IM_MOVI_0002,               
 NU_AUXI, TI_ORIG, TI_DATO )               
 SELECT T1.CO_EMPR, T1.TI_ENTI, T1.CO_ENTI, T1.DE_ENTI,               
 T1.CO_BANC_ENRE, T1.NU_CNTA_ENRE, T1.AA_ENRE, T1.MM_ENRE, T1.NU_COMP_ENRE,               
 "E", T5.FE_OPER, T1.NU_REND_GAST,              
 T1.CO_CAJA, T1.AA_CAJA, T1.MM_CAJA, T1.NU_COMP_CAJA,              
 T1.TI_MOVI, T1.FE_OPER, T1.CO_TIPO_OPER, T3.CO_MONE,               
 ROUND(              
 ( PATINDEX(T3.CO_MONE, "'+@ISCO_MONE+'") +              
 PATINDEX("0", CONVERT( CHAR(1), PATINDEX(T3.CO_MONE, "'+@ISCO_MONE+'")))*             
 ( PATINDEX(T3.CO_MONE, T4.CO_MONE_DEFA)/T1.FA_TIPO_CAMB  +              
   PATINDEX(T3.CO_MONE, T4.CO_MONE_EXTR)*T1.FA_TIPO_CAMB ))*ISNULL(T1.IM_MOVI,0) , 2),               
 ( PATINDEX(T1.TI_MOVI,"E")- PATINDEX(T1.TI_MOVI,"I")) , "C", "2"              
 FROM TCMOVI_CAJA T1, TTTIPO_OPER T2, TMCAJA T3, TMPARA_TESO T4, TCMOVI_BANC T5              
 WHERE T1.CO_EMPR = T2.CO_EMPR              
 '+ @ISCA_WHER_LOCA + @VSCA_SELE +@VSCA_CAJA_0002 + '              
 AND T1.FE_OPER <= CONVERT(DATETIME, "'+ @VSFE_FINA +'", 103)             
 AND T1.TI_SITU != "ANU"              
 AND T5.TI_SITU != "ANU"              
 AND T1.CO_TIPO_OPER = T2.CO_TIPO_OPER              
 AND T2.ST_ENRE_OTRO = "S"              
 AND T1.CO_EMPR = T3.CO_EMPR              
 AND T1.CO_BANC_ENRE IS NOT NULL              
 AND T1.NU_CNTA_ENRE IS NOT NULL              
 AND T1.CO_EMPR = T4.CO_EMPR              
 AND T1.CO_CAJA_ENRE IS NOT NULL              
 AND T1.AA_ENRE IS NOT NULL                      
 AND T1.MM_ENRE IS NOT NULL               
 AND T1.NU_COMP_ENRE IS NOT NULL              


 AND T1.CO_EMPR = T5.CO_EMPR              
 AND T1.CO_BANC_ENRE = T5.CO_BANC              
 AND T1.NU_CNTA_ENRE = T5.NU_CNTA_BANC              
 AND T1.AA_ENRE = T5.AA_BNCO              
 AND T1.MM_ENRE = T5.MM_BNCO              
 AND T1.NU_COMP_ENRE = T5.NU_COMP_BANC              
 AND T5.TI_MOVI_BANC = "E" ')               

--RENDICIONES              
 EXECUTE (              
 'INSERT #TWCNTA_CRTE(CO_EMPR, TI_ENTI, CO_ENTI, DE_ENTI,               
 CO_CABA_0001, NU_CNTA_0001, AA_0001, MM_0001, NU_COMP_0001,               
 TI_MOVI_0001, FE_OPER_0001, NU_REND_GAST,               
 FE_OPER_0002, IM_MOVI_0002,               
 TI_DOCU_0002, NU_DOCU_0002, CO_BCHE_0002, NU_CHEQ_0002,              
 NU_AUXI, TI_ORIG, TI_DATO)               
 SELECT MAX(T2.CO_EMPR), T1.TI_ENTI, T1.CO_ENTI, MAX(T1.DE_NOMB_ORDE),               
 MAX(T2.CO_BANC_DEST), MAX(T2.NU_CNTA_DEST), MAX(T2.AA_OPER_DEST), MAX(T2.MM_OPER_DEST),  MAX(T2.NU_COMP_DEST),               
 MAX(T1.TI_MOVI_BANC), MAX(T1.FE_OPER), T2.NU_REND_GAST,               
 MAX(T2.FE_REND_GAST),                
 ROUND(              
 SUM(( PATINDEX(T4.CO_MONE, "'+@ISCO_MONE+'") +              
 PATINDEX("0", CONVERT( CHAR(1), PATINDEX(T4.CO_MONE, "'+@ISCO_MONE+'")))*              
 ( PATINDEX(T4.CO_MONE, T5.CO_MONE_DEFA)/T3.FA_CAMB_DOCU  +              
   PATINDEX(T4.CO_MONE, T5.CO_MONE_EXTR)*T3.FA_CAMB_DOCU ))*ISNULL(T3.IM_CONV,0)), 2),               
 MAX(T1.CO_TIPO_DOCU), MAX(T1.NU_DOCU_BANC), MAX(T1.CO_BANC_CHEQ), MAX(T1.NU_CHEQ),              
 -1, "R", "2"              
 FROM TCREND_GAST T2, TCMOVI_BANC T1, TDREND_GAST T3, TMCNTA_BANC T4, TMPARA_TESO T5--, TREMPR_GRUP T8, TMUSUA T9               
 WHERE T1.TI_MOVI_BANC = "E"               
 '+ @VSCA_SELE + @VSCA_BANC_0001 + '              
 AND T1.FE_OPER <= CONVERT(DATETIME, "'+ @VSFE_FINA +'", 103)              
 AND T1.TI_SITU != "ANU"              
 AND T2.TI_SITU != "ANU"              
 AND T2.CO_EMPR = T1.CO_EMPR              
 AND T2.AA_OPER_DEST = T1.AA_BNCO              
 AND T2.MM_OPER_DEST = T1.MM_BNCO              
 AND T2.CO_BANC_DEST = T1.CO_BANC              
 AND T2.NU_CNTA_DEST = T1.NU_CNTA_BANC              
 AND T2.NU_COMP_DEST = T1.NU_COMP_BANC              
 '+ @ISCA_WHER_LOCA + '              
 AND T2.CO_EMPR = T3.CO_EMPR              
 AND T2.NU_REND_GAST = T3.NU_REND_GAST              
 AND T2.FE_REND_GAST = T3.FE_REND_GAST              
 AND T1.CO_EMPR = T4.CO_EMPR              
 AND T1.CO_BANC = T4.CO_BANC              
 AND T1.NU_CNTA_BANC = T4.NU_CNTA_BANC              
 AND T1.CO_EMPR = T5.CO_EMPR             
 GROUP BY T2.NU_REND_GAST, T1.TI_ENTI, T1.CO_ENTI')              
END              



IF @ISTI_REPO = 'T' OR @ISTI_REPO = 'C'              
BEGIN              
-- ENTREGAS A RENDIR (HACER JOIN CON TIPO DE OPERACION)              
    EXECUTE (              
 'INSERT #TWCNTA_CRTE(CO_EMPR, TI_ENTI, CO_ENTI, DE_ENTI,               
 CO_CABA_0001, AA_0001, MM_0001, NU_COMP_0001,               
 TI_MOVI_0001, FE_OPER_0001, TI_OPER_0001, CO_MONE_0001, IM_MOVI_0001, NU_AUXI,               
 TI_ORIG, TI_DATO, DE_NOMB_BANC)               
 SELECT T1.CO_EMPR, T1.TI_ENTI, T1.CO_ENTI, T1.DE_ENTI,               
 T1.CO_CAJA, T1.AA_CAJA, T1.MM_CAJA, T1.NU_COMP_CAJA,               
 T1.TI_MOVI, T1.FE_OPER, T1.CO_TIPO_OPER, T3.CO_MONE,               
 ROUND(              
 ( PATINDEX(T3.CO_MONE, "'+@ISCO_MONE+'") +              
 PATINDEX("0", CONVERT( CHAR(1), PATINDEX(T3.CO_MONE, "'+@ISCO_MONE+'")))*              
 ( PATINDEX(T3.CO_MONE, T4.CO_MONE_DEFA)/T1.FA_TIPO_CAMB  +              
   PATINDEX(T3.CO_MONE, T4.CO_MONE_EXTR)*T1.FA_TIPO_CAMB ))*ISNULL(T1.IM_MOVI,0), 2),               
 1, "C", "1", T3.DE_CAJA              
 FROM TCMOVI_CAJA T1, TTTIPO_OPER T2, TMCAJA T3, TMPARA_TESO T4              
 WHERE T1.TI_MOVI = "E"              
 '+ @ISCA_WHER_LOCA + @VSCA_SELE + @VSCA_CAJA_0001 + '              
 AND T1.FE_OPER <= CONVERT(DATETIME, "'+ @VSFE_FINA +'", 103)              
 AND T1.TI_SITU != "ANU"              
 AND T1.CO_EMPR = T2.CO_EMPR              
 AND T1.CO_TIPO_OPER = T2.CO_TIPO_OPER              
 AND T2.ST_ENRE_OTRO = "S"              
 AND T1.CO_EMPR = T3.CO_EMPR              
 AND T1.CO_CAJA = T3.CO_CAJA              
 AND T1.CO_EMPR = T4.CO_EMPR              
 AND T1.CO_CAJA_ENRE IS NULL              
 AND T1.AA_ENRE IS NULL                      

 AND T1.MM_ENRE IS NULL               
 AND T1.NU_COMP_ENRE IS NULL              
 AND T1.NU_REND_GAST IS NULL')               

-- EGRESOS/INGRESOS -> REEMBOLSOS/DEVOLUCIONES (HACER JOIN CON TIPO DE OPERACION)              

 --POR BANCOS              
 EXECUTE (              
 'INSERT #TWCNTA_CRTE(CO_EMPR, TI_ENTI, CO_ENTI, DE_ENTI,               
 CO_CABA_0001, NU_CNTA_0001, AA_0001, MM_0001, NU_COMP_0001,               
 TI_MOVI_0001, FE_OPER_0001, NU_REND_GAST,               
 CO_CABA_0002, AA_0002, MM_0002, NU_COMP_0002,               
 TI_MOVI_0002, FE_OPER_0002, TI_OPER_0002, CO_MONE_0002,              
 IM_MOVI_0002,               
 NU_AUXI, TI_ORIG, TI_DATO )               

 SELECT T1.CO_EMPR, T1.TI_ENTI, T1.CO_ENTI, T1.DE_ENTI,               
 T1.CO_BANC_ENRE, T1.NU_CNTA_ENRE, T1.AA_ENRE, T1.MM_ENRE, T1.NU_COMP_ENRE,               
 "E", T5.FE_OPER, T1.NU_REND_GAST,              
 T1.CO_CAJA, T1.AA_CAJA, T1.MM_CAJA, T1.NU_COMP_CAJA,              
 T1.TI_MOVI, T1.FE_OPER, T1.CO_TIPO_OPER, T3.CO_MONE,               
 ROUND(              
 ( PATINDEX(T3.CO_MONE, "'+@ISCO_MONE+'") +              
 PATINDEX("0", CONVERT( CHAR(1), PATINDEX(T3.CO_MONE, "'+@ISCO_MONE+'")))*              
 ( PATINDEX(T3.CO_MONE, T4.CO_MONE_DEFA)/T1.FA_TIPO_CAMB  +              
   PATINDEX(T3.CO_MONE, T4.CO_MONE_EXTR)*T1.FA_TIPO_CAMB ))*ISNULL(T1.IM_MOVI,0), 2),               
 ( PATINDEX(T1.TI_MOVI,"E")- PATINDEX(T1.TI_MOVI,"I")) , "C", "2"              
 FROM TCMOVI_CAJA T1, TTTIPO_OPER T2, TMCAJA T3, TMPARA_TESO T4, TCMOVI_BANC T5    
 WHERE T1.CO_EMPR = T2.CO_EMPR              
 '+ @ISCA_WHER_LOCA + @VSCA_SELE +@VSCA_CAJA_0002 + '              
 AND T1.FE_OPER <= CONVERT(DATETIME, "'+ @VSFE_FINA +'", 103)              
 AND T1.TI_SITU != "ANU"              
 AND T5.TI_SITU != "ANU"              
 AND T1.CO_TIPO_OPER = T2.CO_TIPO_OPER              
 AND T2.ST_ENRE_OTRO = "S"              

 AND T1.CO_EMPR = T3.CO_EMPR              
 AND T1.CO_CAJA = T3.CO_CAJA              

 AND T1.CO_EMPR = T4.CO_EMPR              
 AND T1.CO_BANC_ENRE IS NOT NULL              
 AND T1.NU_CNTA_ENRE IS NOT NULL              
 AND T1.AA_ENRE IS NOT NULL                      
 AND T1.MM_ENRE IS NOT NULL               
 AND T1.NU_COMP_ENRE IS NOT NULL              


 AND T1.CO_EMPR = T5.CO_EMPR             AND T1.CO_BANC_ENRE = T5.CO_BANC              

 AND T1.NU_CNTA_ENRE = T5.NU_CNTA_BANC              
 AND T1.AA_ENRE = T5.AA_BNCO              
 AND T1.MM_ENRE = T5.MM_BNCO              
 AND T1.NU_COMP_ENRE = T5.NU_COMP_BANC              
 AND T5.TI_MOVI_BANC = "E" ')              

 -- POR CAJA              
 EXECUTE (              
 'INSERT #TWCNTA_CRTE(CO_EMPR, TI_ENTI, CO_ENTI, DE_ENTI,               
 CO_CABA_0001, AA_0001, MM_0001, NU_COMP_0001,               
 TI_MOVI_0001, FE_OPER_0001, NU_REND_GAST,               
 CO_CABA_0002, AA_0002, MM_0002, NU_COMP_0002,               
 TI_MOVI_0002, FE_OPER_0002, TI_OPER_0002, CO_MONE_0002,              
 IM_MOVI_0002, NU_AUXI, TI_ORIG, TI_DATO )               

 SELECT T1.CO_EMPR, T1.TI_ENTI, T1.CO_ENTI, T1.DE_ENTI,               
 T1.CO_CAJA_ENRE, T1.AA_ENRE, T1.MM_ENRE, T1.NU_COMP_ENRE,               
 "E", T5.FE_OPER, T1.NU_REND_GAST,              
 T1.CO_CAJA, T1.AA_CAJA, T1.MM_CAJA, T1.NU_COMP_CAJA,              
 T1.TI_MOVI, T1.FE_OPER, T1.CO_TIPO_OPER, T3.CO_MONE,               
 ROUND(              
 ( PATINDEX(T3.CO_MONE, "'+@ISCO_MONE+'") +              
 PATINDEX("0", CONVERT( CHAR(1), PATINDEX(T3.CO_MONE, "'+@ISCO_MONE+'")))*              
 ( PATINDEX(T3.CO_MONE, T4.CO_MONE_DEFA)/T1.FA_TIPO_CAMB  +              
   PATINDEX(T3.CO_MONE, T4.CO_MONE_EXTR)*T1.FA_TIPO_CAMB ))*ISNULL(T1.IM_MOVI,0), 2),               
 ( PATINDEX(T1.TI_MOVI,"E")- PATINDEX(T1.TI_MOVI,"I")) , "C", "2"              
 FROM TCMOVI_CAJA T1, TTTIPO_OPER T2, TMCAJA T3, TMPARA_TESO T4, TCMOVI_CAJA T5              
 WHERE T1.CO_EMPR = T2.CO_EMPR              
 '+ @ISCA_WHER_LOCA + @VSCA_SELE +@VSCA_CAJA_0002 + '              
 AND T1.FE_OPER <= CONVERT(DATETIME, "'+ @VSFE_FINA +'", 103)              
 AND T1.TI_SITU != "ANU"              
 AND T5.TI_SITU != "ANU"              
 AND T1.CO_TIPO_OPER = T2.CO_TIPO_OPER              
 AND T2.ST_ENRE_OTRO = "S"              

 AND T1.CO_EMPR = T3.CO_EMPR              
 AND T1.CO_CAJA = T3.CO_CAJA              

 AND T1.CO_EMPR = T4.CO_EMPR              

 AND T1.CO_CAJA_ENRE IS NOT NULL              
 AND T1.AA_ENRE IS NOT NULL                      
 AND T1.MM_ENRE IS NOT NULL               
 AND T1.NU_COMP_ENRE IS NOT NULL              


 AND T1.CO_EMPR = T5.CO_EMPR              
 AND T1.CO_CAJA_ENRE = T5.CO_CAJA              
 AND T1.AA_ENRE = T5.AA_CAJA              
 AND T1.MM_ENRE = T5.MM_CAJA              
 AND T1.NU_COMP_ENRE = T5.NU_COMP_CAJA              
 AND T5.TI_MOVI = "E" ')               

--RENDICIONES              
 EXECUTE (              
 'INSERT #TWCNTA_CRTE(CO_EMPR, TI_ENTI, CO_ENTI, DE_ENTI,               
 CO_CABA_0001, AA_0001, MM_0001, NU_COMP_0001,               
 TI_MOVI_0001, FE_OPER_0001, NU_REND_GAST,               
 FE_OPER_0002, IM_MOVI_0002, NU_AUXI, TI_ORIG, TI_DATO)               
 SELECT MAX(T2.CO_EMPR), T1.TI_ENTI, T1.CO_ENTI, MAX(T1.DE_ENTI),               
 MAX(T2.CO_CAJA_DEST), MAX(T2.AA_OPER_DEST), MAX(T2.MM_OPER_DEST),  MAX(T2.NU_COMP_DEST),               
 MAX(T1.TI_MOVI), MAX(T1.FE_OPER), T2.NU_REND_GAST,               
 MAX(T2.FE_REND_GAST),               
 ROUND(              
 SUM(( PATINDEX(T4.CO_MONE, "'+@ISCO_MONE+'") +              
 PATINDEX("0", CONVERT( CHAR(1), PATINDEX(T4.CO_MONE, "'+@ISCO_MONE+'")))*              
 ( PATINDEX(T4.CO_MONE, T5.CO_MONE_DEFA)/T3.FA_CAMB_DOCU  +              
   PATINDEX(T4.CO_MONE, T5.CO_MONE_EXTR)*T3.FA_CAMB_DOCU ))*ISNULL(T3.IM_CONV,0)), 2),               
 -1, "R", "2"              
 FROM TCREND_GAST T2, TCMOVI_CAJA T1, TDREND_GAST T3, TMCAJA T4, TMPARA_TESO T5--, TREMPR_GRUP T8, TMUSUA T9               

 WHERE T1.TI_MOVI = "E"               
 '+ @VSCA_SELE + @VSCA_CAJA_0001 + '              
 AND T1.FE_OPER <= CONVERT(DATETIME, "'+ @VSFE_FINA +'", 103)              
 AND T1.TI_SITU != "ANU"              
 AND T2.TI_SITU != "ANU"              
 AND T2.CO_EMPR = T1.CO_EMPR              
 AND T2.AA_OPER_DEST = T1.AA_CAJA              
 AND T2.MM_OPER_DEST = T1.MM_CAJA              
 AND T2.CO_CAJA_DEST = T1.CO_CAJA              
 '+ @ISCA_WHER_LOCA + '              
 AND T2.NU_COMP_DEST = T1.NU_COMP_CAJA              
 AND T2.CO_EMPR = T3.CO_EMPR              
 AND T2.NU_REND_GAST = T3.NU_REND_GAST              
 AND T2.FE_REND_GAST = T3.FE_REND_GAST              
 AND T1.CO_EMPR = T4.CO_EMPR              
 AND T1.CO_CAJA = T4.CO_CAJA              
 AND T1.CO_EMPR = T5.CO_EMPR              
 GROUP BY T2.NU_REND_GAST, T1.TI_ENTI, T1.CO_ENTI')              
END              



SELECT T1.CO_EMPR, MAX(T2.DE_NOMB), T1.TI_ENTI, MAX(T3.NO_TIPO_AUXI), T1.CO_ENTI, MAX(T1.DE_ENTI),               
NULL, NULL,               
NULL,               
NULL, NULL, NULL, NULL,               
NULL, NULL, NULL, NULL,              
NULL, NULL, NULL, NULL,              
NULL,               
NULL, NULL, NULL, NULL, NULL,               
NULL, NULL, NULL, NULL,              
NULL,               
NULL, NULL, NULL, NULL,              
NULL, NULL,  NULL, ROUND(ISNULL(SUM(ISNULL(T1.IM_MOVI_0001,0)*ISNULL(T1.NU_AUXI,0)),0)              
+ ISNULL(SUM(ISNULL(T1.IM_MOVI_0002,0)*ISNULL(T1.NU_AUXI,0)),0),2),              
NULL              
FROM #TWCNTA_CRTE T1, TMEMPR T2, TTAUXI_EMPR T3               
WHERE T1.FE_OPER_0001 < @IDFE_INIC              
AND T1.CO_EMPR = T2.CO_EMPR              
AND T1.CO_EMPR = T3.CO_EMPR              
AND RTRIM(T1.TI_ENTI) = RTRIM(T3.TI_AUXI_EMPR)              
GROUP BY T1.CO_EMPR, T1.TI_ENTI, T1.CO_ENTI              

UNION ALL               

SELECT T1.CO_EMPR, T2.DE_NOMB, T1.TI_ENTI, T3.NO_TIPO_AUXI, T1.CO_ENTI, T1.DE_ENTI,               
T1.CO_CABA_0001, T1.NU_CNTA_0001,               
T1.AA_0001+T1.MM_0001+T1.NU_COMP_0001+T1.TI_MOVI_0001,               
T1.AA_0001, T1.MM_0001, T1.NU_COMP_0001, T1.TI_MOVI_0001,               
T1.FE_OPER_0001, T1.TI_OPER_0001, T1.CO_MONE_0001, T1.IM_MOVI_0001,              
T1.TI_DOCU_0001, T1.NU_DOCU_0001, T1.CO_BCHE_0001, T1.NU_CHEQ_0001,              
T1.NU_REND_GAST,               
T1.CO_CABA_0002, T1.NU_CNTA_0002, T1.AA_0002, T1.MM_0002, T1.NU_COMP_0002,               
T1.TI_MOVI_0002, T1.FE_OPER_0002, T1.TI_OPER_0002, T1.CO_MONE_0002,              
T1.IM_MOVI_0002,               
T1.TI_DOCU_0002, T1.NU_DOCU_0002, T1.CO_BCHE_0002, T1.NU_CHEQ_0002,              
T1.NU_AUXI, T1.TI_ORIG,  TI_DATO, NULL,              
T1.DE_NOMB_BANC              
FROM #TWCNTA_CRTE T1, TMEMPR T2, TTAUXI_EMPR T3               
WHERE T1.FE_OPER_0001 >= @IDFE_INIC              
AND T1.CO_EMPR = T2.CO_EMPR              
AND T1.CO_EMPR = T3.CO_EMPR              
AND T1.TI_ENTI = T3.TI_AUXI_EMPR              


ORDER BY 1, 3, 5, 7, 8, 9, 39, 14, 29, 38 DESC             

RETURN              

/********************************* FIN ***********************************/ 
GO
